package com.ruoyi.system.utils;

import com.ruoyi.common.utils.DateUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * SQL脚本备份生成器
 * 
 * @author ruoyi
 */
public class SqlBackupGenerator {
    
    private static final Logger log = LoggerFactory.getLogger(SqlBackupGenerator.class);
    
    // 批量插入的记录数
    private static final int BATCH_SIZE = 1000;
    
    // 需要转义的字符
    private static final Map<String, String> ESCAPE_CHARS = new HashMap<>();
    
    static {
        ESCAPE_CHARS.put("\\", "\\\\");
        ESCAPE_CHARS.put("'", "\\'");
        ESCAPE_CHARS.put("\"", "\\\"");
        ESCAPE_CHARS.put("\n", "\\n");
        ESCAPE_CHARS.put("\r", "\\r");
        ESCAPE_CHARS.put("\t", "\\t");
        ESCAPE_CHARS.put("\b", "\\b");
        ESCAPE_CHARS.put("\f", "\\f");
    }
    
    /**
     * 数据库类型枚举
     */
    public enum DatabaseType {
        MYSQL, POSTGRESQL, ORACLE, SQLSERVER, DAMENG
    }

    /**
     * 备份配置
     */
    public static class BackupConfig {
        private boolean includeStructure = true;  // 是否包含表结构
        private boolean includeData = true;       // 是否包含数据
        private boolean dropTables = true;        // 是否生成DROP TABLE语句
        private boolean addComments = true;       // 是否添加注释
        private List<String> includeTables;       // 包含的表列表，为空则备份所有表
        private List<String> excludeTables;       // 排除的表列表
        private String charset = "utf8mb4";       // 字符集
        private DatabaseType databaseType = DatabaseType.MYSQL; // 数据库类型
        
        // Getters and Setters
        public boolean isIncludeStructure() { return includeStructure; }
        public void setIncludeStructure(boolean includeStructure) { this.includeStructure = includeStructure; }
        
        public boolean isIncludeData() { return includeData; }
        public void setIncludeData(boolean includeData) { this.includeData = includeData; }
        
        public boolean isDropTables() { return dropTables; }
        public void setDropTables(boolean dropTables) { this.dropTables = dropTables; }
        
        public boolean isAddComments() { return addComments; }
        public void setAddComments(boolean addComments) { this.addComments = addComments; }
        
        public List<String> getIncludeTables() { return includeTables; }
        public void setIncludeTables(List<String> includeTables) { this.includeTables = includeTables; }
        
        public List<String> getExcludeTables() { return excludeTables; }
        public void setExcludeTables(List<String> excludeTables) { this.excludeTables = excludeTables; }
        
        public String getCharset() { return charset; }
        public void setCharset(String charset) { this.charset = charset; }

        public DatabaseType getDatabaseType() { return databaseType; }
        public void setDatabaseType(DatabaseType databaseType) { this.databaseType = databaseType; }
    }
    
    /**
     * 备份进度回调接口
     */
    public interface ProgressCallback {
        void onProgress(String currentTable, int tableIndex, int totalTables, long processedRows, long totalRows);
        void onTableStart(String tableName, long totalRows);
        void onTableComplete(String tableName, long processedRows);
        void onError(String message, Exception e);
    }
    
    /**
     * 生成数据库备份SQL脚本
     * 
     * @param connection 数据库连接
     * @param databaseName 数据库名
     * @param outputFile 输出文件路径
     * @param config 备份配置
     * @param callback 进度回调
     * @return 是否成功
     */
    public static boolean generateBackup(Connection connection, String databaseName, 
                                       String outputFile, BackupConfig config, 
                                       ProgressCallback callback) {
        try (PrintWriter writer = new PrintWriter(new OutputStreamWriter(
                new FileOutputStream(outputFile), "UTF-8"))) {
            
            // 写入文件头
            writeHeader(writer, databaseName, config);
            
            // 获取要备份的表列表
            List<String> tables = getTableList(connection, config);
            
            if (tables.isEmpty()) {
                log.warn("没有找到需要备份的表");
                return false;
            }
            
            log.info("开始备份数据库 [{}]，共 {} 个表", databaseName, tables.size());
            
            // 备份每个表
            for (int i = 0; i < tables.size(); i++) {
                String tableName = tables.get(i);
                try {
                    if (callback != null) {
                        long totalRows = getTableRowCount(connection, tableName);
                        callback.onTableStart(tableName, totalRows);
                    }
                    
                    backupTable(connection, tableName, writer, config, callback, i, tables.size());
                    
                    if (callback != null) {
                        long processedRows = getTableRowCount(connection, tableName);
                        callback.onTableComplete(tableName, processedRows);
                    }
                    
                } catch (Exception e) {
                    log.error("备份表 [{}] 失败", tableName, e);
                    if (callback != null) {
                        callback.onError("备份表 " + tableName + " 失败: " + e.getMessage(), e);
                    }
                    // 继续备份其他表
                }
            }
            
            // 写入文件尾
            writeFooter(writer, config);
            
            log.info("数据库备份完成，文件保存至: {}", outputFile);
            return true;
            
        } catch (Exception e) {
            log.error("生成数据库备份失败", e);
            if (callback != null) {
                callback.onError("生成数据库备份失败: " + e.getMessage(), e);
            }
            return false;
        }
    }
    
    /**
     * 写入文件头
     */
    private static void writeHeader(PrintWriter writer, String databaseName, BackupConfig config) {
        if (config.isAddComments()) {
            writer.println("-- --------------------------------------------------------");
            writer.println("-- 数据库备份文件");
            writer.println("-- 生成时间: " + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            writer.println("-- 数据库: " + databaseName);
            writer.println("-- 生成工具: 若依数据同步系统 SQL备份生成器");
            writer.println("-- --------------------------------------------------------");
            writer.println();
        }
        
        writer.println("SET FOREIGN_KEY_CHECKS=0;");
        writer.println("SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";");
        writer.println("SET time_zone = \"+00:00\";");
        writer.println();
        
        if (config.isAddComments()) {
            writer.println("-- 设置字符集");
        }
        writer.println("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;");
        writer.println("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;");
        writer.println("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;");
        writer.println("/*!40101 SET NAMES " + config.getCharset() + " */;");
        writer.println();
    }
    
    /**
     * 写入文件尾
     */
    private static void writeFooter(PrintWriter writer, BackupConfig config) {
        writer.println();
        writer.println("SET FOREIGN_KEY_CHECKS=1;");
        writer.println();
        
        writer.println("/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;");
        writer.println("/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;");
        writer.println("/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;");
        
        if (config.isAddComments()) {
            writer.println();
            writer.println("-- 备份完成");
        }
    }
    
    /**
     * 获取要备份的表列表
     */
    private static List<String> getTableList(Connection connection, BackupConfig config) throws SQLException {
        List<String> tables = new ArrayList<>();

        String sql = getShowTablesSQL(config.getDatabaseType());
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                String tableName = rs.getString(1);

                // 检查包含列表
                if (config.getIncludeTables() != null && !config.getIncludeTables().isEmpty()) {
                    if (!config.getIncludeTables().contains(tableName)) {
                        continue;
                    }
                }

                // 检查排除列表
                if (config.getExcludeTables() != null && config.getExcludeTables().contains(tableName)) {
                    continue;
                }

                tables.add(tableName);
            }
        }

        return tables;
    }

    /**
     * 根据数据库类型获取显示表的SQL
     */
    private static String getShowTablesSQL(DatabaseType dbType) {
        switch (dbType) {
            case MYSQL:
                return "SHOW TABLES";
            case POSTGRESQL:
                return "SELECT tablename FROM pg_tables WHERE schemaname = 'public'";
            case ORACLE:
                return "SELECT table_name FROM user_tables";
            case SQLSERVER:
                return "SELECT name FROM sys.tables WHERE type = 'U'";
            case DAMENG:
                return "SELECT table_name FROM user_tables";
            default:
                return "SHOW TABLES";
        }
    }
    
    /**
     * 获取表的行数
     */
    private static long getTableRowCount(Connection connection, String tableName) {
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM `" + tableName + "`")) {

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            log.warn("获取表 [{}] 行数失败", tableName, e);
        }
        return 0;
    }

    /**
     * 备份单个表
     */
    private static void backupTable(Connection connection, String tableName, PrintWriter writer,
                                  BackupConfig config, ProgressCallback callback,
                                  int tableIndex, int totalTables) throws SQLException {

        if (config.isAddComments()) {
            writer.println("-- --------------------------------------------------------");
            writer.println("-- 表: " + tableName);
            writer.println("-- --------------------------------------------------------");
            writer.println();
        }

        // 备份表结构
        if (config.isIncludeStructure()) {
            backupTableStructure(connection, tableName, writer, config);
        }

        // 备份表数据
        if (config.isIncludeData()) {
            backupTableData(connection, tableName, writer, config, callback, tableIndex, totalTables);
        }

        writer.println();
    }

    /**
     * 备份表结构
     */
    private static void backupTableStructure(Connection connection, String tableName,
                                           PrintWriter writer, BackupConfig config) throws SQLException {

        // 生成DROP TABLE语句
        if (config.isDropTables()) {
            String dropSql = getDropTableSQL(tableName, config.getDatabaseType());
            writer.println(dropSql);
        }

        // 获取CREATE TABLE语句
        String createTableSql = getCreateTableSQL(connection, tableName, config.getDatabaseType());
        if (createTableSql != null && !createTableSql.isEmpty()) {
            writer.println(createTableSql + ";");
            writer.println();
        }
    }

    /**
     * 根据数据库类型获取DROP TABLE语句
     */
    private static String getDropTableSQL(String tableName, DatabaseType dbType) {
        switch (dbType) {
            case MYSQL:
                return "DROP TABLE IF EXISTS `" + tableName + "`;";
            case POSTGRESQL:
                return "DROP TABLE IF EXISTS \"" + tableName + "\" CASCADE;";
            case ORACLE:
                return "DROP TABLE \"" + tableName + "\" CASCADE CONSTRAINTS;";
            case SQLSERVER:
                return "IF OBJECT_ID('[" + tableName + "]', 'U') IS NOT NULL DROP TABLE [" + tableName + "];";
            case DAMENG:
                return "DROP TABLE IF EXISTS \"" + tableName + "\" CASCADE;";
            default:
                return "DROP TABLE IF EXISTS `" + tableName + "`;";
        }
    }

    /**
     * 根据数据库类型获取CREATE TABLE语句
     */
    private static String getCreateTableSQL(Connection connection, String tableName, DatabaseType dbType) throws SQLException {
        switch (dbType) {
            case MYSQL:
                String sql = "SHOW CREATE TABLE `" + tableName + "`";
                try (Statement stmt = connection.createStatement();
                     ResultSet rs = stmt.executeQuery(sql)) {
                    if (rs.next()) {
                        return rs.getString(2);
                    }
                }
                break;
            case POSTGRESQL:
            case ORACLE:
            case SQLSERVER:
            case DAMENG:
                // 对于其他数据库类型，使用DatabaseMetaData生成CREATE TABLE语句
                return generateCreateTableSQL(connection, tableName, dbType);
            default:
                return generateCreateTableSQL(connection, tableName, dbType);
        }
        return null;
    }

    /**
     * 使用DatabaseMetaData生成CREATE TABLE语句
     */
    private static String generateCreateTableSQL(Connection connection, String tableName, DatabaseType dbType) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ");

        // 根据数据库类型添加表名引号
        switch (dbType) {
            case MYSQL:
                sql.append("`").append(tableName).append("`");
                break;
            case POSTGRESQL:
            case ORACLE:
            case DAMENG:
                sql.append("\"").append(tableName).append("\"");
                break;
            case SQLSERVER:
                sql.append("[").append(tableName).append("]");
                break;
            default:
                sql.append("`").append(tableName).append("`");
        }

        sql.append(" (\n");

        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet rs = metaData.getColumns(null, null, tableName, null)) {
            boolean first = true;
            while (rs.next()) {
                if (!first) {
                    sql.append(",\n");
                }
                first = false;

                String columnName = rs.getString("COLUMN_NAME");
                String columnType = rs.getString("TYPE_NAME");
                int columnSize = rs.getInt("COLUMN_SIZE");
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                boolean nullable = rs.getBoolean("NULLABLE");

                sql.append("  ");

                // 根据数据库类型添加列名引号
                switch (dbType) {
                    case MYSQL:
                        sql.append("`").append(columnName).append("`");
                        break;
                    case POSTGRESQL:
                    case ORACLE:
                    case DAMENG:
                        sql.append("\"").append(columnName).append("\"");
                        break;
                    case SQLSERVER:
                        sql.append("[").append(columnName).append("]");
                        break;
                    default:
                        sql.append("`").append(columnName).append("`");
                }

                sql.append(" ").append(columnType);

                // 添加长度信息
                if (needsSize(columnType)) {
                    sql.append("(").append(columnSize);
                    if (decimalDigits > 0) {
                        sql.append(",").append(decimalDigits);
                    }
                    sql.append(")");
                }

                // 添加NOT NULL约束
                if (!nullable) {
                    sql.append(" NOT NULL");
                }
            }
        }

        sql.append("\n)");

        return sql.toString();
    }

    /**
     * 判断字段类型是否需要长度信息
     */
    private static boolean needsSize(String columnType) {
        String type = columnType.toUpperCase();
        return type.contains("VARCHAR") || type.contains("CHAR") ||
               type.contains("DECIMAL") || type.contains("NUMERIC");
    }

    /**
     * 备份表数据
     */
    private static void backupTableData(Connection connection, String tableName, PrintWriter writer,
                                      BackupConfig config, ProgressCallback callback,
                                      int tableIndex, int totalTables) throws SQLException {

        // 获取表的总行数
        long totalRows = getTableRowCount(connection, tableName);
        if (totalRows == 0) {
            if (config.isAddComments()) {
                writer.println("-- 表 " + tableName + " 无数据");
                writer.println();
            }
            return;
        }

        if (config.isAddComments()) {
            writer.println("-- 导出表 " + tableName + " 的数据，共 " + totalRows + " 行");
        }

        // 分页查询数据
        long processedRows = 0;
        int offset = 0;

        // 用于存储列信息，第一次查询时获取
        List<ColumnInfo> columns = null;
        StringBuilder columnNames = null;

        while (processedRows < totalRows) {
            String dataSql = "SELECT * FROM `" + tableName + "` LIMIT " + offset + ", " + BATCH_SIZE;

            try (Statement stmt = connection.createStatement();
                 ResultSet rs = stmt.executeQuery(dataSql)) {

                // 第一次查询时，从ResultSet获取列信息
                if (columns == null) {
                    columns = getColumnsFromResultSet(rs);
                    if (columns.isEmpty()) {
                        log.warn("表 [{}] 没有列信息", tableName);
                        return;
                    }

                    // 构建列名字符串
                    columnNames = new StringBuilder();
                    for (int i = 0; i < columns.size(); i++) {
                        if (i > 0) columnNames.append(", ");
                        columnNames.append("`").append(columns.get(i).getName()).append("`");
                    }
                }

                List<String> insertValues = new ArrayList<>();

                while (rs.next()) {
                    StringBuilder values = new StringBuilder("(");

                    for (int i = 0; i < columns.size(); i++) {
                        if (i > 0) values.append(", ");

                        ColumnInfo column = columns.get(i);
                        Object value = null;

                        try {
                            value = rs.getObject(i + 1);
                        } catch (SQLException e) {
                            log.warn("获取表 [{}] 第 {} 列数据失败: {}", tableName, i + 1, e.getMessage());
                            value = null;
                        }

                        if (value == null) {
                            values.append("NULL");
                        } else {
                            values.append(formatValue(value, column));
                        }
                    }

                    values.append(")");
                    insertValues.add(values.toString());
                    processedRows++;

                    // 更新进度
                    if (callback != null) {
                        callback.onProgress(tableName, tableIndex, totalTables, processedRows, totalRows);
                    }
                }

                // 写入INSERT语句
                if (!insertValues.isEmpty()) {
                    writer.println("INSERT INTO `" + tableName + "` (" + columnNames + ") VALUES");

                    for (int i = 0; i < insertValues.size(); i++) {
                        writer.print(insertValues.get(i));
                        if (i < insertValues.size() - 1) {
                            writer.println(",");
                        } else {
                            writer.println(";");
                        }
                    }
                    writer.println();
                }

                offset += BATCH_SIZE;
            }
        }
    }

    /**
     * 从ResultSet获取列信息（推荐方法，确保与查询结果一致）
     */
    private static List<ColumnInfo> getColumnsFromResultSet(ResultSet rs) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            ColumnInfo column = new ColumnInfo();
            column.setName(metaData.getColumnName(i));
            column.setType(metaData.getColumnType(i));
            column.setTypeName(metaData.getColumnTypeName(i));
            columns.add(column);
        }

        return columns;
    }

    /**
     * 获取表的列信息（备用方法）
     */
    private static List<ColumnInfo> getTableColumns(Connection connection, String tableName) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();

        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet rs = metaData.getColumns(null, null, tableName, null)) {
            while (rs.next()) {
                ColumnInfo column = new ColumnInfo();
                column.setName(rs.getString("COLUMN_NAME"));
                column.setType(rs.getInt("DATA_TYPE"));
                column.setTypeName(rs.getString("TYPE_NAME"));
                columns.add(column);
            }
        }

        return columns;
    }

    /**
     * 格式化字段值
     */
    private static String formatValue(Object value, ColumnInfo column) {
        if (value == null) {
            return "NULL";
        }

        // 根据数据类型进行格式化
        switch (column.getType()) {
            case Types.VARCHAR:
            case Types.CHAR:
            case Types.LONGVARCHAR:
            case Types.CLOB:
            case Types.NVARCHAR:
            case Types.NCHAR:
            case Types.LONGNVARCHAR:
            case Types.NCLOB:
                return "'" + escapeString(value.toString()) + "'";

            case Types.DATE:
            case Types.TIME:
            case Types.TIMESTAMP:
                return "'" + value.toString() + "'";

            case Types.BLOB:
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.LONGVARBINARY:
                // 二进制数据转换为十六进制
                if (value instanceof byte[]) {
                    return "0x" + bytesToHex((byte[]) value);
                } else {
                    return "'" + escapeString(value.toString()) + "'";
                }

            default:
                // 数值类型直接返回
                return value.toString();
        }
    }

    /**
     * 转义字符串
     */
    private static String escapeString(String str) {
        if (str == null) return "";

        StringBuilder sb = new StringBuilder();
        for (char c : str.toCharArray()) {
            String escaped = ESCAPE_CHARS.get(String.valueOf(c));
            if (escaped != null) {
                sb.append(escaped);
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    /**
     * 字节数组转十六进制字符串
     */
    private static String bytesToHex(byte[] bytes) {
        StringBuilder sb = new StringBuilder();
        for (byte b : bytes) {
            sb.append(String.format("%02x", b));
        }
        return sb.toString();
    }

    /**
     * 列信息类
     */
    private static class ColumnInfo {
        private String name;
        private int type;
        private String typeName;

        public String getName() { return name; }
        public void setName(String name) { this.name = name; }

        public int getType() { return type; }
        public void setType(int type) { this.type = type; }

        public String getTypeName() { return typeName; }
        public void setTypeName(String typeName) { this.typeName = typeName; }
    }
}
